from pathlib import Path 
import re 
from datetime import date 

from .database import create_curs
from .config import get_settings


settings = get_settings()


def get_rows(sql: str)  : 
    
    with create_curs() as curs: 
        curs.execute(sql)
        rows = curs.fetchall()

    return rows 
   

def filter_field(row: tuple) -> tuple[str, str]: 
    return (row[1], row[7])


def clean_filename(name):
    # 替换Windows文件名中不允许的字符为下划线
    name = str(name)
    invalid_chars = r'[<>:"/\\|?*:]'  # 添加了冒号:
    cleaned_name = re.sub(invalid_chars, '_', name)
    # 移除开头和结尾的空格和点号
    cleaned_name = cleaned_name.strip().strip('.')
    # 确保名称不为空
    if not cleaned_name:
        cleaned_name = '未知'
    return cleaned_name  


def create_file(rows: list, root_dir: Path) -> None: 
    # 根据日期创建一个根目录
    sub_dir = root_dir / (str(date.today()) + settings.sub_dir)
    sub_dir.mkdir(exist_ok=True)

    # 遍历记录集
    for row in rows: 
        res = filter_field(row)
        name, category = res 

        if not category: 
            category = '未知'
        
        name = clean_filename(name) 
        
        child_dir = sub_dir / category 
        child_dir.mkdir(exist_ok=True)
      

        file_dir = child_dir / (name + '-待')
        file_dir.mkdir(exist_ok=True)

    
    
def main(): 
    
    # create dir 
    root_dir: Path = Path(__file__).parents[1] / settings.root_dir
    root_dir.mkdir(exist_ok=True)
    sql = 'select * from 商品资料' 

    rows = get_rows(sql) 
    if not rows: 
        return '没有查询到记录' 
    
    create_file(rows, root_dir)


if __name__ == '__main__': 
    main()